package com.css.fxfzfxqh.modules.synthesis.repository.impl;

import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.casualtiesDE.entity.CasualtiesDECalculation;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.economicLossDE.entity.EconomicLossEDCalculation;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.casualtiesPE.entity.CasualtiesPECalculation;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.economicLossPE.entity.EconomicLossPDCalculation;
import com.css.fxfzfxqh.modules.comprehensiveRiskZoneSet.model.entity.FxqhComprehensiveRiskZoneSetSet;
import com.css.fxfzfxqh.modules.synthesis.entity.PopulationAndLoss;
import com.css.fxfzfxqh.modules.synthesis.param.CheckParam;
import com.css.fxfzfxqh.modules.synthesis.param.QhSynthesisExportParam;
import com.css.fxfzfxqh.modules.synthesis.param.QueryParam;
import com.css.fxfzfxqh.modules.synthesis.param.StatisticsParam;
import com.css.fxfzfxqh.modules.synthesis.repository.AssessRepository;
import com.css.fxfzfxqh.modules.synthesis.vo.QhSynthesisExportVO;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class AssessRepositoryImpl implements AssessRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public Boolean checkData1(CheckParam param) {
        String sql = "select * from fxqh_casualties_calculation_ed where del_flag = '0' and assess_id = ? and flag = ? limit 1";
        List<CasualtiesDECalculation> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CasualtiesDECalculation.class), param.getId(), param.getSeismicInputMode());
        if (query.size() == 0) return false;
        return true;
    }

    @Override
    public Boolean checkData2(CheckParam param) {
        if ("0".equals(param.getSeismicInputMode())) param.setSeismicInputMode("11");
        if ("1".equals(param.getSeismicInputMode())) param.setSeismicInputMode("21");
        if ("2".equals(param.getSeismicInputMode())) param.setSeismicInputMode("31");
        String sql = "select * from fxqh_economic_loss_calculation_ed where assess_id = ? and condition_type = ? limit 1";
        List<EconomicLossEDCalculation> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EconomicLossEDCalculation.class), param.getId(), param.getSeismicInputMode());
        if (query.size() == 0) return false;
        return true;
    }

    @Override
    public Boolean checkData3(CheckParam param) {
        String sql = "select * from fxqh_casualties_calculation_pd where del_flag = '0' and assess_id = ? limit 1";
        List<CasualtiesPECalculation> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CasualtiesPECalculation.class), param.getId());
        if (query.size() == 0) return false;
        return true;
    }

    @Override
    public Boolean checkData4(CheckParam param) {
        String sql = "select * from fxqh_economic_loss_calculation_pd where del_flag = '0' and assess_id = ? limit 1";
        List<EconomicLossPDCalculation> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EconomicLossPDCalculation.class), param.getId());
        if (query.size() == 0) return false;
        return true;
    }


    @Override
    public List<PopulationAndLoss> getData1(CheckParam param) {
        String sql = "select a.*,b.loss_sum from (\n" +
                "select province_name province,city_name city,county_name county,flag category,case intensity when 'Ⅵ' then 'ld1' when 'Ⅶ' then 'ld2' when 'Ⅷ' then 'ld3' when 'Ⅸ' then 'ld4' when 'Ⅹ' then 'ld5' end as type,sum(night_death_correction_value) population_sum from fxqh_casualties_calculation_ed where del_flag = '0' and assess_id = '" + param.getId() + "' and flag = '0' group by province_name,city_name,county_name,flag,intensity ) a left join (\n" +
                "select province_name province,city_name city,county_name county,condition as type,condition_type category,sum(correction_value) loss_sum from fxqh_economic_loss_calculation_ed where assess_id = '" + param.getId() + "' and condition_type = '11' group by province_name,city_name,county_name,condition,condition_type ) b on a.type = b.type and a.county = b.county";
        List<PopulationAndLoss> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PopulationAndLoss.class));
        return query;
    }

    @Override
    public List<FxqhComprehensiveRiskZoneSetSet> getSetUp() {
        String sql = "select * from fxqh_comprehensive_risk_zone_set where type in (1,2,3,4) order by type";
        List<FxqhComprehensiveRiskZoneSetSet> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FxqhComprehensiveRiskZoneSetSet.class));
        return query;
    }

    @Override
    public void saveResult(List<PopulationAndLoss> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO fxqh_composite_assess(id, province, city, county, population_sum, loss_sum, category, type, total, level, task_id, create_user, create_time, geom)" +
                " values(:id, :province, :city, :county, :populationSum, :lossSum, :category, :type, :total, :level, :taskId, :createUser, :createTime,  ST_GeometryFromText(:geom, 4490))";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);

    }

    @Override
    public List<PopulationAndLoss> getData2(CheckParam param) {
        String sql = "select a.*,b.loss_sum from (\n" +
                "select province_name province,city_name city,county_name county,flag category, condition as type,sum(night_death_correction_value) population_sum from fxqh_casualties_calculation_ed where del_flag = '0' and assess_id = '" + param.getId() + "' and flag = '1' group by province_name,city_name,county_name,flag,condition ) a left join (\n" +
                "select province_name province,city_name city,county_name county,condition as type,condition_type category,sum(correction_value) loss_sum from fxqh_economic_loss_calculation_ed where assess_id = '" + param.getId() + "' and condition_type = '21' group by province_name,city_name,county_name,condition,condition_type) b on a.type = b.type and a.county = b.county";
        List<PopulationAndLoss> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PopulationAndLoss.class));
        return query;
    }

    @Override
    public List<PopulationAndLoss> getData3(CheckParam param) {
        String sql = "select a.*,b.loss_sum from (\n" +
                "select province_name province,city_name city,county_name county,flag category, '' as type,sum(night_death_correction_value) population_sum from fxqh_casualties_calculation_ed where del_flag = '0' and assess_id = '" + param.getId() + "' and flag = '2' group by province_name,city_name,county_name,flag ) a left join (\n" +
                "select province_name province,city_name city,county_name county,condition as type,condition_type category,sum(correction_value) loss_sum from fxqh_economic_loss_calculation_ed where assess_id = '" + param.getId() + "' and condition_type = '31' group by province_name,city_name,county_name,condition,condition_type) b on a.county = b.county";
        List<PopulationAndLoss> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PopulationAndLoss.class));
        return query;
    }

    @Override
    public List<PopulationAndLoss> getData4(CheckParam param) {
        String sql = "select a.*,b.loss_sum from (" +
                "select province_name province,city_name city,county_name county,'3' category,years as type,sum(correction_value::FLOAT) population_sum from fxqh_casualties_calculation_pd where assess_id = '" + param.getId() + "' and del_flag = '0' and time = 'sjd2' AND casualties = 'rysw2' group by province_name,city_name,county_name,years ) a left join (" +
                "select province_name province,city_name city,county_name county,'3' category,years as type,sum(correction_value::FLOAT) loss_sum from fxqh_economic_loss_calculation_pd where assess_id = '" + param.getId() + "' and del_flag = '0' group by province_name,city_name,county_name,years) b on a.county = b.county and a.type = b.type";
        List<PopulationAndLoss> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PopulationAndLoss.class));
        return query;
    }

    @Override
    public Map<String, Object> getResults(QueryParam param, int page, int size) {
        StringBuilder sql = new StringBuilder("select * from fxqh_composite_assess where task_id = '" + param.getId() + "' and category = '" + param.getCategory() + "'");
        if (PlatformObjectUtils.isNotEmpty(param.getType())){
            sql.append(" and type = '" + param.getType() + "'");
        }
        int total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PopulationAndLoss.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sql.append(limitSql);
        List<PopulationAndLoss> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PopulationAndLoss.class));
        Map<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", list);
        return resultMap;
    }

    @Override
    public void delete(CheckParam param) {
        String sql = "delete from fxqh_composite_assess where  task_id = '" + param.getId() + "' and category = '" + param.getSeismicInputMode() + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteByTaskId(String taskId, String inputMode){
        String sql = "delete from fxqh_composite_assess where  task_id = '" + taskId + "' and category='"+inputMode+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<StatisticsParam> statistics(QueryParam param) {
        StringBuilder sql = new StringBuilder("select level,count(1) count from fxqh_composite_assess where task_id = '" + param.getId() + "' and category = '" + param.getCategory() + "'");
        if (PlatformObjectUtils.isNotEmpty(param.getType())){
            sql.append(" and type = '" + param.getType() + "'");
        }
        sql.append(" group by level");
        List<StatisticsParam> statisticsParams = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsParam.class));
        return statisticsParams;
    }

    @Override
    public List<QhSynthesisExportVO> getByParam(QhSynthesisExportParam param) {
        String sql="select province, city, county, type condition, level from fxqh_composite_assess where 1=1";
        if (PlatformObjectUtils.isNotEmpty(param.getId())){
            sql=sql+" and task_id='"+param.getId()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())){
            sql=sql+" and province='"+param.getProvince()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())){
            sql=sql+" and city='"+param.getCity()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())){
            sql=sql+" and county='"+param.getCounty()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCategory())){
            sql=sql+" and category='"+param.getCategory()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getType())){
            sql=sql+" and type='"+param.getType()+"'";
        }
        List<QhSynthesisExportVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(QhSynthesisExportVO.class));
        return query;
    }
}
